create or replace package p1 as

type tstud is record(
  nume char(30), prenume char(30), grupa char(10)
);

type tdisc is record(
  cod char(10), denumire char(100), media number, nr number
);

type cstudenti is ref cursor return tstud;

type cdisc is ref cursor return tdisc;

procedure nrstud(nr out number);
  --nr.studenti in baza de date
procedure nrstud(an in number, nr out number);
  --nr.studenti ce apar intr-o formatie intr-un an universitar
function nrgrupe(an in number, s in number) return number;
  --nr.grupe care intr-un an universitar au fost intr-o sectie s
procedure listastud(an in number, s in number, stud out cstudenti);
  --lista stud. care intr-un an univ.sunt la o sectie data
procedure listadisc(s in number, an in number, m in number, disc1 out cdisc, disc2 out cdisc);
  --lista disciplinelor predate la o sectie s, care intr-un an univ.au media (pt.studentii care au nota la aceasta disciplina) mai mare, respectiv mai mica, decat o valoare m data
procedure extragestud(stud cstudenti);
  --extrage continutul argumentului (se foloseste in PL/Sql)
procedure extragedisc(disc cdisc);
  --extrage continutul argumentului (se foloseste in PL/Sql)
end;
/

create or replace package body p1 is

procedure extragestud(stud cstudenti) is
s stud%rowtype;
n number:=0;
begin
  fetch stud into s;
  while stud%found loop
    n:=n+1;
    dbms_output.put_line(to_char(n,'999') || '. ' || s.nume || s.prenume || s.grupa);
    fetch stud into s;
  end loop;
end extragestud;

procedure extragedisc(disc cdisc) is
d disc%rowtype;
n number:=0;
begin
  fetch disc into d;
  while disc%found loop
    n:=n+1;
    dbms_output.put_line(to_char(n,'999') || '. ' || d.cod || d.denumire || 'Media:' || to_char(d.media,'99.99') || ' nr.note:' || to_char(d.nr));
    fetch disc into d;
  end loop;
end extragedisc;

procedure nrstud(nr out number) is
begin
  select count(*) into nr from studenti;
end nrstud;

procedure nrstud(an in number, nr out number) is
begin
  select count(*) into nr 
    from studenti s inner join traiectorie t 
    on s.sectia=t.sectia and s.nrmatricol=t.nrmatricol
    where anuniv=an;
end nrstud;

function nrgrupe(an in number, s in number) return number is
nr number;
begin
  select count(*) into nr 
    from grupe
    where anuniv=an and sectia=s;
  return nr;
end;

procedure listastud(an in number, s in number, stud out cstudenti) is
begin
  open stud for 
    select nume,prenume,grupa 
    from studenti st inner join traiectorie t 
    on st.sectia=t.sectia and st.nrmatricol=t.nrmatricol
    where anuniv=an and st.sectia=s;
end listastud;

procedure listadisc(s in number, an in number, m in number, disc1 out cdisc, disc2 out cdisc) is
begin
  open disc1 for
    select * from
      (select cod, denumire, TRUNC(avg(nota),5) as mg, TRUNC(count(*),5) as nr
      from discipline inner join rezultate on discipline.cod=rezultate.disciplina
      where anuniv=an and nota>0 and sectia=s
      group by cod,denumire)
    where mg>m;
	-- este necesara functia TRUNC pt. a fi folosita de Ado.Net, altfel da eroare
  open disc2 for
    select * from
      (select cod, denumire, TRUNC(avg(nota),5) as mg, TRUNC(count(*),5) as nr
      from discipline inner join rezultate on discipline.cod=rezultate.disciplina
      where anuniv=an and nota>0 and sectia=s
      group by cod,denumire)
    where mg<=m;
end listadisc;

end p1;